set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0009.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on   
go
/* BEGIN UPDATE SCRIPT */

--=======================================================
-- SCRIPT TO CREATE TOPIC CATEGORY MODULE
--=======================================================

------- CREATE MODULE AND SUBMENU --------------
DELETE MODULES WHERE MODULE_NAME='TopicCategories'
go
INSERT INTO [dbo].[MODULES]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [MODULE_ENABLED], [TAB_ENABLED], [MOBILE_ENABLED], [TAB_ORDER], [PORTAL_ENABLED], [CUSTOM_ENABLED], [REPORT_ENABLED], [IMPORT_ENABLED], [SYNC_ENABLED], [IS_ADMIN], [CUSTOM_PAGING], [MASS_UPDATE_ENABLED], [DEFAULT_SEARCH_ENABLED], [TABLE_NAME], [EXCHANGE_SYNC], [EXCHANGE_FOLDERS], [EXCHANGE_CREATE_PARENT])
SELECT N'bcbd5654-d3f3-47e6-bb8a-1669b1f16dbc', 0, NULL, '20111210 16:37:20.643', NULL, '20111210 16:37:20.643', '20111210 16:37:20.643', N'TopicCategories', N'.moduleList.TopicCategories', N'~/TopicCategories/', 1, 1, 1, 40, 0, 0, 0, 0, 0, 1, 1, 0, 1, N'TOPIC_CATEGORIES', 0, 0, 0
go
delete dbo.SHORTCUTS where MODULE_NAME='TopicCategories'
INSERT INTO [dbo].[SHORTCUTS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [IMAGE_NAME], [SHORTCUT_ENABLED], [SHORTCUT_ORDER], [SHORTCUT_MODULE], [SHORTCUT_ACLTYPE])
SELECT N'B1944B75-1F03-4D15-BCBC-B6531BF1E353', 0, NULL, getdate(), NULL, getdate(), getdate(), N'TopicCategories', N'Topics.LNK_TOPIC_LIST', N'~/TopicCategories/default.aspx', N'Campaigns.gif', 1, 1, N'TopicCategories', N'edit' UNION ALL
SELECT N'4A4D6F09-BCD0-437A-8563-BE333EEC23C4', 0, NULL, getdate(), NULL, getdate(), getdate(), N'TopicCategories', N'Topics.LNK_NEW_TOPIC', N'~/TopicCategories/edit.aspx', N'CreateCampaigns.gif', 1, 1, N'TopicCategories', N'edit'
GO
--------------  GRID VIEW ----------------------------------
DELETE DBO.GRIDVIEWS WHERE ID='AF990F0B-FFE0-4C98-A180-18267E61060B'
GO
INSERT INTO [dbo].[GRIDVIEWS]
           (ID
           ,[DELETED]
           ,[CREATED_BY]
           ,[DATE_ENTERED]
           ,[MODIFIED_USER_ID]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[NAME]
           ,[MODULE_NAME]
           ,[VIEW_NAME]
           ,[PRE_LOAD_EVENT_ID]
           ,[POST_LOAD_EVENT_ID])
     VALUES
           ('AF990F0B-FFE0-4C98-A180-18267E61060B'
           ,0
           ,null
           ,getdate()
           ,null
           ,getdate()
           ,getdate()
           ,'TopicCategories.ListView'
           ,'TopicCategories'
           ,'vwTOPIC_CATEGORIES_List'
           ,null
           ,null)
GO
DELETE GRIDVIEWS_COLUMNS WHERE ID='D0799FA7-70FC-41D6-9496-B79CF2BAC692'
GO
INSERT INTO [dbo].[GRIDVIEWS_COLUMNS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[GRID_NAME]
           ,[COLUMN_INDEX]
           ,[COLUMN_TYPE]
           ,[DEFAULT_VIEW]
           ,[HEADER_TEXT]
           ,[SORT_EXPRESSION]
           ,[ITEMSTYLE_WIDTH]
           ,[ITEMSTYLE_CSSCLASS]
           ,[ITEMSTYLE_WRAP]
           ,[DATA_FIELD]
           ,[DATA_FORMAT]
           ,[URL_FIELD]
           ,[URL_FORMAT]
           )
     VALUES  
           ('D0799FA7-70FC-41D6-9496-B79CF2BAC692'
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'TopicCategories.ListView'
           ,1
           ,'TemplateColumn'
           ,0
           ,'TopicCategories.LBL_LIST_NAME'
           ,'NAME'
           ,'25%'
           ,'listViewTdLinkS1'
           ,1
           ,'NAME'
           ,'Hyperlink'
           ,'ID'
           ,'~/topicCategories/edit.aspx?id={0}')
GO

DELETE GRIDVIEWS_COLUMNS WHERE ID='1102CBC2-6C96-4AE2-BB81-E0EAFD5AA3D1'
INSERT INTO [dbo].[GRIDVIEWS_COLUMNS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[GRID_NAME]
           ,[COLUMN_INDEX]
           ,[COLUMN_TYPE]
           ,[DEFAULT_VIEW]
           ,[HEADER_TEXT]
           ,[SORT_EXPRESSION]
           ,[ITEMSTYLE_WIDTH]
           ,[ITEMSTYLE_CSSCLASS]
           ,[ITEMSTYLE_WRAP]
           ,[DATA_FIELD]
           ,[DATA_FORMAT]
           ,[URL_FIELD]
           ,[URL_FORMAT]
           )
     VALUES 
           ('1102CBC2-6C96-4AE2-BB81-E0EAFD5AA3D1'
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'TopicCategories.ListView'
           ,2
           ,'TemplateColumn'
           ,0
           ,'Topics.LBL_LIST_DESCRIPTION'
           ,'DESCRIPTION'
           ,'25%'
           ,'listViewTdLinkS1'
           ,1
           ,'DESCRIPTION'
           ,NULL
           ,NULL
           ,NULL)
GO

---------------------------------------------------------------------------
--------------- CREATE DB VIEWS: GENERAL, EDIT, LIST ---------------------- 

drop view [dbo].[vwTOPIC_CATEGORIES]
go
CREATE VIEW [dbo].[vwTOPIC_CATEGORIES]
AS
SELECT     ID, DELETED, CREATED_BY, DATE_ENTERED, MODIFIED_USER_ID, DATE_MODIFIED, DATE_MODIFIED_UTC, NAME, PARENT_ID, DESCRIPTION, 
                      SORT_ORDER
FROM         dbo.TOPICS 
where PARENT_ID is null
go

drop view [dbo].[vwTOPIC_CATEGORIES_EDIT]
go
CREATE VIEW [dbo].[vwTOPIC_CATEGORIES_EDIT]
AS
SELECT     ID, DELETED, CREATED_BY, DATE_ENTERED, MODIFIED_USER_ID, DATE_MODIFIED, DATE_MODIFIED_UTC, NAME, PARENT_ID, DESCRIPTION, 
                      SORT_ORDER
FROM         dbo.vwTOPIC_CATEGORIES
go


drop view [dbo].[vwTOPIC_CATEGORIES_List]
go
CREATE VIEW [dbo].[vwTOPIC_CATEGORIES_List]
AS
SELECT     ID, DELETED, CREATED_BY, DATE_ENTERED, MODIFIED_USER_ID, DATE_MODIFIED, DATE_MODIFIED_UTC, NAME, PARENT_ID, DESCRIPTION, 
                      SORT_ORDER
FROM         dbo.[vwTOPIC_CATEGORIES] 
GO


------------------------------------------------------------
--------------  EDIT VIEW FOR TOPIC CATEGORIES -------------
	---------- INSERT EDIT VIEW -------
delete EDITVIEWS where id='D198AE3C-1D31-4D8E-BF59-DEFD4FE11683'
go
INSERT INTO [dbo].[EDITVIEWS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[NAME]
           ,[MODULE_NAME]
           ,[VIEW_NAME]
           ,[LABEL_WIDTH]
           ,[FIELD_WIDTH]
           )
     VALUES
           ('D198AE3C-1D31-4D8E-BF59-DEFD4FE11683'
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'TopicCategories.EditView'
           ,'TopicCategories'
           ,'vwTOPIC_CATEGORIES_Edit'
           ,'15%'
           ,'35%'
           )
GO
	---------- INSERT FIELD : NAME -------
DELETE EDITVIEWS_FIELDS WHERE ID= 'A4E126E2-FBA2-4CFB-BC4F-5128A548E0B5'
go
INSERT INTO [dbo].[EDITVIEWS_FIELDS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[EDIT_NAME]
           ,[FIELD_INDEX]
           ,[FIELD_TYPE]
           ,[DEFAULT_VIEW]
           ,[DATA_LABEL]
           ,[DATA_FIELD]
           ,[DATA_REQUIRED]
           ,[UI_REQUIRED]
           ,[FORMAT_TAB_INDEX]
           ,[FORMAT_MAX_LENGTH]
           ,[FORMAT_SIZE]
           ,[FORMAT_ROWS]
           ,[FORMAT_COLUMNS]
           ,[COLSPAN]
           )
     VALUES
           ('A4E126E2-FBA2-4CFB-BC4F-5128A548E0B5'
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'TopicCategories.EditView'
           ,0
           ,'TextBox'
           ,0
           ,'TopicCategories.LBL_Name'
           ,'NAME'
           ,1
           ,1
           ,1
           ,200
           ,80
           ,null
           ,NULL
           ,3
           )
GO
	---------- INSERT FIELD : DESCRIPTION -------
DELETE EDITVIEWS_FIELDS WHERE ID= 'E99524D4-D78F-4961-8A75-CCDD162933AE'
GO
INSERT INTO [dbo].[EDITVIEWS_FIELDS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[EDIT_NAME]
           ,[FIELD_INDEX]
           ,[FIELD_TYPE]
           ,[DEFAULT_VIEW]
           ,[DATA_LABEL]
           ,[DATA_FIELD]
           ,[DATA_REQUIRED]
           ,[UI_REQUIRED]
           ,[FORMAT_TAB_INDEX]
           ,[FORMAT_MAX_LENGTH]
           ,[FORMAT_SIZE]
           ,[FORMAT_ROWS]
           ,[FORMAT_COLUMNS]
           ,[COLSPAN]
           )
     VALUES
           ('E99524D4-D78F-4961-8A75-CCDD162933AE'
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'TopicCategories.EditView'
           ,1
           ,'TextBox'
           ,0
           ,'TopicCategories.LBL_Description'
           ,'DESCRIPTION'
           ,0
           ,0
           ,3
           ,200
           ,80
           ,null
           ,80
           ,3
           )
GO
	--------- INSERT TERMONOLOGY -----------
	
DELETE TERMINOLOGY WHERE ID='12EF8756-83A5-4296-8294-2882E7C47DB4'
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   ('12EF8756-83A5-4296-8294-2882E7C47DB4'
	   ,'LBL_Description'
	   ,'TopicCategories'
	   ,'Description'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO
DELETE TERMINOLOGY WHERE ID='ED8D2C92-E758-4805-A18D-6DA2D26E2DF4'
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   ('ED8D2C92-E758-4805-A18D-6DA2D26E2DF4'
	   ,'LBL_Name'
	   ,'TopicCategories'
	   ,'Name'
	   ,'en-US',0,getdate(),getdate(),getdate())

	----------  INSERT DYNAMIC BUTTON -----------------
GO
delete DYNAMIC_BUTTONS where id='87E0E25B-4FBC-4C6A-9918-0DC69FF0FCC0'
GO
INSERT INTO [dbo].[DYNAMIC_BUTTONS] ([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [VIEW_NAME], [CONTROL_INDEX], [CONTROL_TYPE], [DEFAULT_VIEW], [MODULE_NAME], [MODULE_ACCESS_TYPE], [TARGET_NAME], [TARGET_ACCESS_TYPE], [MOBILE_ONLY], [ADMIN_ONLY], [EXCLUDE_MOBILE], [CONTROL_TEXT], [CONTROL_TOOLTIP], [CONTROL_ACCESSKEY], [CONTROL_CSSCLASS], [TEXT_FIELD], [ARGUMENT_FIELD], [COMMAND_NAME], [URL_FORMAT], [URL_TARGET], [ONCLICK_SCRIPT])
Values (N'87E0E25B-4FBC-4C6A-9918-0DC69FF0FCC0', 0, NULL, getdate(), NULL, getdate(),getdate(), N'TopicCategories.EditView', 1, N'ButtonLink', 0, N'TopicCategories', NULL, NULL, NULL, 0, 0, 0, N'.LBL_CANCEL_BUTTON_LABEL', N'.LBL_CANCEL_BUTTON_TITLE', N'.LBL_CANCEL_BUTTON_KEY', N'button', N'ID', NULL, N'Cancel', N'default.aspx', NULL, NULL)
GO
delete DYNAMIC_BUTTONS where id='51E5C718-81B1-41FE-9EB4-2295A23F5EDD'
go
INSERT INTO [dbo].[DYNAMIC_BUTTONS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [VIEW_NAME], [CONTROL_INDEX], [CONTROL_TYPE], [DEFAULT_VIEW], [MODULE_NAME], [MODULE_ACCESS_TYPE], [TARGET_NAME], [TARGET_ACCESS_TYPE], [MOBILE_ONLY], [ADMIN_ONLY], [EXCLUDE_MOBILE], [CONTROL_TEXT], [CONTROL_TOOLTIP], [CONTROL_ACCESSKEY], [CONTROL_CSSCLASS], [TEXT_FIELD], [ARGUMENT_FIELD], [COMMAND_NAME], [URL_FORMAT], [URL_TARGET], [ONCLICK_SCRIPT])
Values (N'51E5C718-81B1-41FE-9EB4-2295A23F5EDD', 0, NULL, getdate(), NULL, getdate(),getdate(), N'TopicCategories.EditView', 0, N'Button', 0, N'TopicCategories', N'edit', NULL, NULL, 0, 0, 0, N'.LBL_SAVE_BUTTON_LABEL', N'.LBL_SAVE_BUTTON_TITLE', N'.LBL_SAVE_BUTTON_KEY', N'button', NULL, NULL, N'Save', NULL, NULL, NULL)

/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
